# !/usr/bin/env python
# -*- coding: utf-8 -*-
import pandas as pd
import pymysql
from sqlalchemy import create_engine

"""
Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': 
not all arguments converted during string formatting

pd.to_sql报错

原因是在使用Pandas写入SQL数据库时，不能使用pymysql.connections库，这个库只能用于查询数据
"""


def write_mysql():
    # data_raw = pd.read_csv(r'./data/data.csv', encoding='utf-8')
    data_raw = pd.read_csv(r'./out/house_price2.csv', encoding='utf-8')
    conn = 'mysql+pymysql://devuser:devuser@172.16.162.20:3306/test?charset=utf8'

    # 将数据写入MySQL，假设表名为'house_price2'，如果表不存在会创建
    data_raw.head(1).to_sql('house_price3', con=conn, if_exists='replace', index=False)


def read_mysql():
    conn = pymysql.connect(host='172.16.162.20', user='devuser', password='devuser', db='test')
    query = "SELECT * FROM test_data limit 3"
    df = pd.read_sql_query(query, conn)
    df2 = pd.read_sql(query, conn)
    conn.close()
    print("read_mysql df", df)
    print("read_mysql df2", df2)


def read_mysql2():
    conn = create_engine('mysql+pymysql://devuser:devuser@172.16.162.20:3306/test?charset=utf8')
    query = "SELECT * FROM test_data limit 3"
    df = pd.read_sql_query(query, conn)
    print("read_mysql2 df", df)


if __name__ == '__main__':
    write_mysql()
    # read_mysql()
    # read_mysql2()
